package cj.web.tb.dao;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;

@DAO
public interface ReportsDAO {
	
	@SQL("SELECT a.name,a.grade,a.teacher,b.gameName,b.num,c.schoolName,c.schoolModel,c.classNum,b.updateAt,b.status,b.id FROM TBRecommendationItem a join TbRecommendation b on a.recommendationId=b.id join TbSchoolRegistration c on b.userId = c.userId WHERE a.deleteFlag=1 AND b.deleteFlag=1 #if(:1 != null){ AND b.gameId =:1 } #if(:2 != null){ AND b.updateAt >= :2 } #if(:3 != null){ AND b.updateAt < :3 } ORDER BY a.recommendationId,b.updateAt DESC LIMIT :4,:5 ")
    List<Map> queryReportsSmtj(Integer gameId,String startAt,String endAt,Integer start,Integer length);
	
	@SQL("SELECT count(1) FROM TBRecommendationItem a join TbRecommendation b on a.recommendationId=b.id join TbSchoolRegistration c on b.userId = c.userId WHERE a.deleteFlag=1 AND b.deleteFlag=1 #if(:1 != null){ AND b.gameId =:1 } #if(:2 != null){ AND b.updateAt >= :2 } #if(:3 != null){ AND b.updateAt < :3 } ORDER BY a.recommendationId,b.updateAt DESC ")
    int queryReportsSmtjSize(Integer gameId,String startAt,String endAt);
	
	@SQL("SELECT b.gameName,c.schoolName,c.schoolModel,c.classNum,b.status,b.num FROM (SELECT gameName,status,SUM(num) as num,userId FROM TbRecommendation WHERE status=4 AND deleteFlag=1 #if(:1 != null){ AND gameId =:1 } #if(:2 != null){ AND updateAt >= :2 } #if(:3 != null){ AND updateAt < :3 } GROUP BY gameName,status,userId ) b JOIN TbSchoolRegistration c ON b.userId = c.userId ORDER BY c.updateAt DESC LIMIT :4,:5 ")
    List<Map> queryReportsSptj(Integer gameId,String startAt,String endAt,Integer start,Integer length);
	
	@SQL("SELECT count(1) FROM (SELECT gameName,status,SUM(num) as num,userId FROM TbRecommendation WHERE status=4 AND deleteFlag=1 #if(:1 != null){ AND gameId =:1 } #if(:2 != null){ AND updateAt >= :2 } #if(:3 != null){ AND updateAt < :3 } GROUP BY gameName,status,userId ) b JOIN TbSchoolRegistration c ON b.userId = c.userId ORDER BY c.updateAt DESC ")
    int queryReportsSptjSize(Integer gameId,String startAt,String endAt);
	
}